Skip Headers

Oracle® XML DB Developer's Guide 10g Release 2 (10.2) Part Number B14259-02 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# 24 Repository Resource Security

This chapter describes the access control list (ACL) based security mechanism for Oracle XML DB resources, how to create ACLs, set and change ACLs on resources, and how ACL security interacts with other Oracle Database security mechanisms.

This chapter contains these topics:

- Overview of Oracle XML DB Resource Security and ACLs

- Access Control List Concepts

- Access Privileges

- Interaction with Database Table Security

- Working with Oracle XML DB ACLs

- Integrating Oracle XML DB with LDAP

- Performance Issues for Using ACLs

## Overview of Oracle XML DB Resource Security and ACLs

Oracle XML DB maintains object-level security for all resources in Oracle XML DB Repository.

Note: XML objects that are not stored in the repository do not have object-level access control.

Oracle XML DB uses an access control list (ACL) mechanism to restrict access to any Oracle XML DB resource or database object mapped to the repository. An ACL is a list of access control entries that determine which principals have access to a given resource or resources. ACLs are a standard security mechanism used in Java, Windows NT, and other systems.

See Also: Chapter 28, "Administering Oracle XML DB" "APIs for XML"

### How the ACL-Based Security Mechanism Works

ACLs in Oracle XML DB are themselves XML schema-based resources, stored and managed in Oracle XML DB. Each resource in Oracle XML DB Repository is protected by an ACL. Before a user performs an operation on a resource, the user privileges on the resource are checked. The set of privileges checked depends on the operation to be performed.

Some ACLs are supplied with Oracle XML DB. There is only one ACL, the bootstrap ACL, located at `/sys/acls/bootstrap_acl.xml` in Oracle XML DB Repository, that is self-protected; that is, it is protected by its own contents. This ACL, supplied with Oracle XML DB, grants READ privilege to all users. The bootstrap ACL also grants FULL ACCESS to XDBADMIN (Oracle XML DB `ADMIN`) and `DBA` roles. The `XDBADMIN` role is particularly useful for users who must register global XML schemas.

Other ACLs supplied with Oracle XML DB include the following. Each is protected by the bootstrap ACL.

- `all_all_acl.xml` Grants all privileges to all users

- `all_owner_acl.xml` Grants all privileges to the owner of the resource

- `ro_all_acl.xml` Grants read privileges to all users

All ACLs must conform to the Oracle XML DB ACL XML schema, which is located in the repository at `/sys/schemas/PUBLIC/xmlns.oracle.com/xdb/acl.xsd`.

All ACLs are stored in table `XDB$ACL`, which is owned by user `XDB`. This is an XML schema-based `XMLType` table. Each row in this table (and therefore each ACL) has a system-generated object identifier (OID) that can be accessed as a column named `OBJECT_ID`.

Each resource has a property named `ACLOID`. The `ACLOID` stores the OID of the ACL that protects the resource. As mentioned, an ACL is itself a resource. Hence, the `XMLRef` property of an ACL resource, for example, `/sys/acls/all_all_acl.xml`, is a `REF` to the row in table `XDB$ACL` that contains the actual content of the ACL. These two properties form the link between table `XDB$RESOURCE`, which stores Oracle XML DB resources, and table `XDB$ACL`.

See Also: Appendix D, "Oracle-Supplied XML Schemas and Examples" for the ACL XML schema

## Access Control List Concepts

This section describes several access control list (ACL) terms and concepts:

- Principal. An entity that may be granted access control privileges to an Oracle XML DB resource. Oracle XML DB supports the following as principals:

  - Database users

  - Database roles. A database role can be understood as a group; for example, the DBA role represents the group of all database administrators.

  - LDAP users and groups. For details on using LDAP principals see "Integrating Oracle XML DB with LDAP".

  The special principal, `dav:owner`, corresponds to the owner of the resource being secured. The owner of the resource is one of the properties of the resource. Use of the `dav:owner` principal allows greater ACL sharing between users, because the owner of the document often has special rights. See Also "Access Privileges".

- Privilege: This is a particular right that can be granted or denied to a principal. Oracle XML DB has a set of system-defined rights (such as `READ` or `UPDATE`) that can be referenced in any ACL. Privileges can be granted or denied to the principal dav:owner, that represents the owner of the document, regardless of who the owner is. Privileges can be one of the following:

  - Aggregate (containing other privileges)

  - Atomic (which cannot be subdivided)

  Aggregate privileges are a naming convenience to simplify usability when the number of privileges becomes large, as well as to promote inter operability between ACL clients. Please see "Access Privileges" for the list of atomic and aggregate privileges that can be used in ACLs.

  The set of privileges granted to a principal controls the ability of that principal to perform a given operation or method on an Oracle XML DB resource. For example, if the principal `HR` wants to perform the `read` operation on a given resource, then the `read` privileges must be granted to `HR` prior to the read operation. Therefore, privileges control how users can operate on resources.

- ACE (access control entry): ACE is an entry in the ACL that grants or denies access to a particular principal (database user). An ACL consists of a list of ACEs where ordering is irrelevant. There can be only one `gran`t `ACE` and one `deny` `ACE` for a particular principal in a single ACL.

  Note: More than one grant ACE (or deny ACE) can apply to a particular user because a user can be granted more than one role.

  An Oracle XML DB ACE element has the following properties:

  - Operation: Either `grant` or `deny`

  - Principal: A valid principal, as described previously.

  - Privileges Set: A particular set of privileges to be granted or denied for a particular principal

  - Principal Format (optional): The format of the principal. An LDAP distinguished name (DN), a short name (DB user/role or LDAP nickname), or an LDAP GUID. The default is `short name`. If the principal name matches both a DB user and an LDAP nickname, it is assumed to refer to the LDAP nickname.

  - Collection (optional): A `BOOLEAN` attribute that specifies whether the principal is a collection of users (LDAP group or DB role) or a single user (LDAP or DB user).

- Access control list (ACL): A list of access control entry elements, with the element name `ace`, that defines access control to a resource. An ACE either grants or denies privileges for a principal.

  The following example shows entries in an ACL:

      <acl description="myacl"
           xmlns="http://xmlns.oracle.com/xdb/acl.xsd"
           xmlns:dav="DAV:"
           xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
           xsi:schemaLocation="http://xmlns.oracle.com/xdb/acl.xsd
                               http://xmlns.oracle.com/xdb/acl.xsd">
        <ace>
          <principal>dav:owner</principal>
          <grant>true</grant>
          <privilege>
            <dav:all/>
          </privilege>
        </ace>
      </acl>

  In this ACL there is only one ACE. The ACE grants all privileges to the owner of the resource.

- Default ACL: When a resource is inserted into Oracle XML DB Repository, by default the ACL on its parent folder is used to protect the resource. After the resource is created, a new ACL can be set on it.

- ACL file-naming conventions: Supplied ACLs use the following file-naming convention: `<privilege>_<users>_acl.xml`

  where `<privilege>` represents the privilege granted and `<users>` represents the users that are granted access to the resource.

- ACL Evaluation Rules: Privileges are checked before a user is allowed to access a resource. This is done by evaluating the resource ACL for the current user. To evaluate an ACL, the database collects the list of ACEs in the ACL that apply to the user logged into the current database session. The list of currently active roles for the user is maintained as part of the session, and it is used to match ACEs, which specify roles as principals, with the current users. To resolve conflicts between ACEs, the following rule is used: if a privilege is denied by any ACE, then the privilege is denied for the entire ACL.

## Access Privileges

Oracle XML DB provides a set of privileges to control access to Oracle XML DB resources. Access privileges in an ACE are stored in the privilege element. Privileges can be either aggregate (composed of other privileges) or atomic.

When an ACL is stored in Oracle XML DB, the aggregate privileges retain their identity: they are not decomposed into the corresponding leaf privileges. In WebDAV terms, these are aggregate privileges that are not abstract.

### Atomic Privileges

Table 24-1 lists the atomic privileges supported by Oracle XML DB.

Table 24-1 Oracle XML DB Supported Atomic Privileges

Privilege Name | Description | Database Counterpart
--- | --- | ---
`read-properties` | Read the properties of a resource | `SELECT`
`read-contents` | Read the contents of a resource | `SELECT`
`update` | Update the properties and contents of a resource | `UPDATE`
`link` | For containers only. Allows resources to be bound to the container. | `INSERT`
`unlink` | For containers only. Allows resources to be unbound from the container. | `DELETE`
`link-to` | Allows resources to be linked | N/A
`unlink-from` | Allows resources to be unlinked | N/A
`read-acl` | Read the resource ACL | `SELECT`
`write-acl-ref` | Changes the resource ID | `UPDATE`
`update-acl` | Change the contents of the resource ACL | `UPDATE`
`resolve` | For containers only: Allows the container to be traversed | `SELECT`
`dav:lock` | Lock a resource using WebDAV locks | `UPDATE`
`dav:unlock` | Unlock a resource locked using a WebDAV lock | `UPDATE`

Note: Privilege names are used as XML element names. Privileges with a `dav:` prefix are part of the WebDAV namespace. Other privileges are part of the Oracle XML DB ACL namespace: `http://xmlns.oracle.com/xdb/acl.xsd`

Because you can directly access the `XMLType` storage for ACLs, the XML structure is part of the client interface. Hence, ACLs can be manipulated using the `XMLType` APIs.

### Aggregate Privileges

Table 24-2 lists the aggregate privileges defined by Oracle XML DB, along with the atomic privileges of which they are composed.

Table 24-2 Aggregate Privileges

Aggregate Privilege Names | Atomic Privileges
--- | ---
`all` | All atomic privileges: `dav:read`, `dav:write`, `dav:read-acl`, `dav:write-acl`, `dav:lock`, `dav:unlock`
`dav:all` | All atomic privileges except `linkto`
`dav:read` | `read-properties`, `read-contents`, `resolve`
`dav:write` | `update`, `link`, `unlink`, `unlink-from`
`dav:read-acl` | `read-acl`
`dav:write-acl` | `write-acl-ref`, `update-acl`

Table 24-3 shows the privileges required for some common operations on resources in Oracle XML DB Repository. The Privileges Required column assumes that you already have the `resolve` privilege on container C and all its parent containers, up to the root of the hierarchy.

Table 24-3 Privileges Needed for Operations on Oracle XML DB Resources

Operation | Description | Privileges Required
--- | --- | ---
`CREATE` | Create a new resource in container C | `update` and `link` on C
`DELETE` | Delete resource R from container C | `update` and `unlinkfrom` on R, `update` and `unlink` on C
`UPDATE` | Update the contents or properties of resources R | `update` on R
`GET` | An FTP or HTTP(S) retrieval of resource R | `read-properties`, `read-contents` on R
`SET_ACL` | Set the ACL of a resource R | `dav:write-acl` on R
`LIST` | List the resources in container C | `read-properties` on C, `read-properties` on resources in C. Only those resources on which the user has `read-properties` privilege are listed.

## Interaction with Database Table Security

Resources in Oracle XML DB Repository are of two types:

- LOB-based (content is stored in a LOB which is part of the resource). Access is determined only by the ACL that protects the resource.

- REF-based (content is XML and is stored in a database table). Users must have the appropriate privilege in the underlying table (or view) where the XML content is stored, as well as permissions through the ACL for the resource.

Since the content of a REF-based resource may actually be stored in a table, it is possible to access this data directly using SQL queries on the table. A uniform access control mechanism is one where the privileges needed are independent of the method of access (for example, FTP, HTTP, or SQL). To provide a uniform security mechanism using ACLs, the underlying table must first be hierarchy-enabled before resources that reference the rows in the table are inserted into Oracle XML DB. This is done using procedure `DBMS_XDBZ.enable_hierarchy`. This procedure adds two hidden columns to store the `ACLOID` and the `OWNER` of the resources that reference the rows in the table. It also adds a Row Level Security (RLS) policy to the table, which checks the ACL whenever a `SELECT`, `UPDATE`, or `DELETE` statement is executed on the table. The default tables produced by XML schema registration are already hierarchy-enabled.

See Also: Oracle Database PL/SQL Packages and Types Reference for information on procedure `DBMS_XDBZ.enable_hierarchy`

In a particular table, some, but not all, objects may be mapped to Oracle XML DB resources. Only those objects mapped into Oracle XML DB Repository undergo ACL checking, but they will all have table-level security.

Note: You cannot hide data in `XMLType` tables from other users when using out-of-line storage. Out-of-line data is not protected by ACL security.

## Working with Oracle XML DB ACLs

Oracle XML DB ACLs are (file) resources, so all of the methods that operate on resources also apply to ACLs. In addition, there are several APIs specific to ACLsin package `DBMS_XDB`. Those procedures and functions let you use PL/SQL to access Oracle XML DB security mechanisms, check user privileges based on a particular ACL, and list the set of privileges the current user has for a particular ACL and resource.

See Also: Chapter 28, "Administering Oracle XML DB"

### Creating an ACL Using DBMS_XDB.createResource

Example 24-1 Creating an ACL Using DBMS_XDB.createResource

This example creates an ACL as file resource `/TESTUSER/acl1.xml`. If applied to a resource, the ACL will grant all privilegest to the owner of a resource.

    DECLARE
      b BOOLEAN;
    BEGIN
      b := DBMS_XDB.createFolder('/TESTUSER');
      b := DBMS_XDB.createResource(
             '/TESTUSER/acl1.xml',
             '<acl description="myacl"
                   xmlns="http://xmlns.oracle.com/xdb/acl.xsd"
                   xmlns:dav="DAV:"
                   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                   xsi:schemaLocation="http://xmlns.oracle.com/xdb/acl.xsd
                                       http://xmlns.oracle.com/xdb/acl.xsd">
                <ace>
                  <principal>dav:owner</principal>
                  <grant>true</grant>
                  <privilege>
                    <dav:all/>
                  </privilege>
                </ace>
              </acl>');
    END;

### Setting the ACL of a Resource

Example 24-2 Setting the ACL of a Resource

This example creates resource `/TESTUSER/po1.xml` and sets its ACL to `/TESTUSER/acl1.xml` using procedure `DBMS_XDB.setACL`.

    DECLARE
      b BOOLEAN;
    BEGIN
      b := DBMS_XDB.createResource('/TESTUSER/po1.xml', 'Hello');
    END;
    /

    CALL DBMS_XDB.setACL('/TESTUSER/po1.xml', '/TESTUSER/acl1.xml');

### Deleting an ACL

Example 24-3 illustrates how to delete an ACL using procedure `DBMS_XDB.deleteResource`.

Example 24-3 Deleting an ACL

This example deletes the ACL created in Example 24-1.

    CALL DBMS_XDB.deleteResource('/TESTUSER/acl1.xml');

If a resource is being protected by an ACL that you will delete, first change the ACL of that resource before deleting the ACL.

### Updating an ACL

This can be done using standard methods for updating resources. In particular, since an ACL is an XML document, SQL function `updateXML` and related XML-updating functions can be used to manipulate ACLs. Oracle XML DB ACLs are cached, for fast evaluation. When a transaction that updates an ACL is committed, the modified ACL is picked up by existing database sessions, after the timeout specified in the Oracle XML DB configuration file, `/xdbconfig.xml`. The XPath location for this timout parameter is `/xdbconfig/sysconfig/acl-max-age`; the value is expressed in seconds. Sessions initiated after the ACL is modified use the new ACL without any delay.If an ACL resource is updated with non-ACL content, the same rules apply as for deletion. Thus, if any resource is being protected by an ACL that is being updated, you must first change the ACL.

See Also: "Updating XML Instances and XML Data in Tables" for information on the SQL functions used here to update XML data

You can use FTP or WebDAV to update an ACL. For more details on how to use these protocols, see Chapter 25, "FTP, HTTP(S), and WebDAV Access to Repository Data". You can update an ACL or an access control entry (ACE) using `RESOURCE_VIEW`.

Example 24-4 Updating (Replacing) an Access Control List

This example uses SQL function `updateXML` to update the ACL `/TESTUSER/acl1.xml` by replacing it entirely. The effect is to replace the principal value `dav:owner` by `TESTUSER`, because the rest of the replacement ACL is the same as it was before.

    UPDATE RESOURCE_VIEW r
      SET r.RES =
            updateXML(
              r.RES,
              '/r:Resource/r:Contents/a:acl',
              '<acl description="myacl"
                    xmlns="http://xmlns.oracle.com/xdb/acl.xsd"
                    xmlns:dav="DAV:"
                    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                    xsi:schemaLocation="http://xmlns.oracle.com/xdb/acl.xsd
                                        http://xmlns.oracle.com/xdb/acl.xsd">
                 <ace>
                   <principal>TESTUSER</principal>
                   <grant>true</grant>
                   <privilege>
                     <dav:all/>
                   </privilege>
                 </ace>
               </acl>',
              'xmlns:r="http://xmlns.oracle.com/xdb/XDBResource.xsd"
               xmlns:a="http://xmlns.oracle.com/xdb/acl.xsd"')
      WHERE r.ANY_PATH='/TESTUSER/acl1.xml';

Example 24-5 Appending ACEs to an Access Control List

This example uses SQL function `appendChildXML` to append an ACE to an existing ACL. The ACE gives privileges `read-properties` and `read-contents` to user hr.

    UPDATE RESOURCE_VIEW r
      SET r.RES =
            appendChildXML(
              r.RES,
              '/r:Resource/r:Contents/a:acl',
              XMLType('<ace xmlns="http://xmlns.oracle.com/xdb/acl.xsd">
                         <principal>HR</principal>
                         <grant>true</grant>
                         <privilege>
                           <read-properties/>
                           <read-contents/>
                         </privilege>
                       </ace>'),
                      'xmlns:r="http://xmlns.oracle.com/xdb/XDBResource.xsd"
                       xmlns:a="http://xmlns.oracle.com/xdb/acl.xsd"')
      WHERE r.ANY_PATH='/TESTUSER/acl1.xml';

Example 24-6 Deleting an ACE from an Access Control List

This examples uses SQL function `deleteXML` to delete an ACE from an ACL.

    UPDATE RESOURCE_VIEW r
      SET r.RES =
            deleteXML(r.RES,
                      '/r:Resource/r:Contents/a:acl/a:ace',
                      'xmlns:r="http://xmlns.oracle.com/xdb/XDBResource.xsd"
                       xmlns:a="http://xmlns.oracle.com/xdb/acl.xsd"')
      WHERE r.ANY_PATH='/TESTUSER/acl1.xml';

### Retrieving the ACL Document for a Given Resource

Example 24-7 illustrates how to use function `DBMS_XDB.getACLDocument` to retrieve the ACL document for a given resource.

Example 24-7 Retrieving the ACL Document for a Resource

    SELECT DBMS_XDB.getACLDocument('/TESTUSER/po1.xml').getClobVal() FROM DUAL;

    DBMS_XDB.GETACLDOCUMENT('/TESTUSER/PO1.XML').GETCLOBVAL()
    --------------------------------------------------------------------------------
    <acl description="myacl" xmlns="http://xmlns.oracle.com/xdb/acl.xsd" xmlns:dav="
    DAV:" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="
    http://xmlns.oracle.com/xdb/acl.xsd                                     http://x
    mlns.oracle.com/xdb/acl.xsd">
      <ace>
        <principal>TESTUSER</principal>
        <grant>true</grant>
        <privilege>
          <dav:all/>
        </privilege>
      </ace>
      <ace xmlns="http://xmlns.oracle.com/xdb/acl.xsd">
        <principal>HR</principal>
        <grant>true</grant>
        <privilege>
          <read-properties/>
          <read-contents/>
        </privilege>
      </ace>
    </acl>

    1 row selected.

### Retrieving Privileges Granted to the Current User for a Particular Resource

Example 24-8 illustrates how to retrieve privileges granted to the current user using function `DBMS_XDB.getPrivileges`.

Example 24-8 Retrieving Privileges Granted to the Current User for a Particular Resource

    SELECT DBMS_XDB.getPrivileges('/TESTUSER/po1.xml').getClobVal() FROM DUAL;

    DBMS_XDB.GETPRIVILEGES('/TESTUSER/PO1.XML').GETCLOBVAL()
    --------------------------------------------------------------------------------
    <privilege xmlns="http://xmlns.oracle.com/xdb/acl.xsd" xmlns:xsi="http://www.w3.
    org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.oracle.com/xdb/acl
    .xsd http://xmlns.oracle.com/xdb/acl.xsd DAV: http://xmlns.oracle.com/xdb/dav.xs
    d" xmlns:xdbacl="http://xmlns.oracle.com/xdb/acl.xsd" xmlns:dav="DAV:">
      <read-properties/>
      <read-contents/>
      <update/>
      <link/>
      <unlink/>
      <read-acl/>
      <write-acl-ref/>
      <update-acl/>
      <resolve/>
      <unlink-from/>
      <dav:lock/>
      <dav:unlock/>
    </privilege>

    1 row selected.

### Checking if the Current User Has Privileges on a Resource

Example 24-9 illustrates how to use function `DBMS_XDB.checkPrivileges` to check if the current user has a given set of privileges on a resource. This function returns a nonzero value if the user has the privileges.

Example 24-9 Checking If a User Has a Certain Privileges on a Resource

This example checks to see if the access privileges `read-contents` and `read-properties` have been granted to the current user on resource `/TESTUSER/po1.xml`. The positive-integer return value shows that they have.

    SELECT DBMS_XDB.checkPrivileges(
             '/TESTUSER/po1.xml',
             XMLType('<privilege
                          xmlns="http://xmlns.oracle.com/xdb/acl.xsd"
                          xmlns:dav="DAV:"
                          xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                          xsi:schemaLocation="http://xmlns.oracle.com/xdb/acl.xsd
                                              http://xmlns.oracle.com/xdb/acl.xsd">
                        <read-contents/>
                        <read-properties/>
                      </privilege>'))
      FROM DUAL;

    DBMS_XDB.CHECKPRIVILEGES('/TESTUSER/PO1.XML',
    ---------------------------------------------
                                                1

    1 row selected.

### Checking if the Current User Has Privileges With the ACL and Resource Owner

Function `DBMS_XDB.ACLCheckPrivileges` is typically used by applications that must perform ACL evaluation on their own, before allowing a user to perform an operation.

Example 24-10 Checking User Privileges using ACLCheckPrivileges

This example checks whether the ACL `/TESTUSER/acl1.xml` grants the privileges `read-contents` and `read-properties` to the current user, `sh`. The second argument, `TESTUSER`, is the user that is substituted for `dav:owner` in the ACL when checking. Since user `sh` does not match any of the users granted the specified privileges, the return value is zero.

    CONNECT SH/SH

    SELECT DBMS_XDB.ACLCheckPrivileges(
             '/TESTUSER/acl1.xml',
             'TESTUSER',
             XMLType('<privilege
                          xmlns="http://xmlns.oracle.com/xdb/acl.xsd"
                          xmlns:dav="DAV:"
                          xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                          xsi:schemaLocation="http://xmlns.oracle.com/xdb/acl.xsd
                                              http://xmlns.oracle.com/xdb/acl.xsd">
                        <read-contents/>
                        <read-properties/>
                      </privilege>'))
      FROM DUAL;

    DBMS_XDB.ACLCHECKPRIVILEGES('/TESTUSER/ACL1.XML','TESTUSER',
    ------------------------------------------------------------
                                                               0

    1 row selected.

### Retrieving the Path of the ACL that Protects a Given Resource

Example 24-11 retrieves the path of the ACL that protects a given resource, by using a `RESOURCE_VIEW` query. The query uses the fact that the `XMLRef` and `ACLOID` elements of the resource form the link between an ACL and a resource.

Example 24-11 Retrieving the Path of the ACL that Protects a Given Resource

This example retrieves the path to an ACL, given a resource protected by the ACL. The ACLOID of a protected resource (`r`) stores the OID of the ACL resource (`a`) that protects it. The `REF` of the ACL resource is the same as that of the object identified by the protected-resource `ACLOID`.

The `REF` of the resource `ACLOID` can be obtained using SQL function `make_ref`, which returns a `REF` to an object-table row with a given OID.

In this example, `make_ref` returns a `REF` to the row of table `XDB$ACL` whose OID is the `/Resource/ACLOID` for the resource `/TESTUSER/po1.xml` (`r`). The inner query returns the `ACLOID` of the resource. The outer query returns the path to the corresponding ACL.

    SELECT a.ANY_PATH
      FROM RESOURCE_VIEW a
      WHERE extractValue(a.RES, '/Resource/XMLRef')
            = make_ref(XDB.XDB$ACL,
                       (SELECT extractValue(r.RES, '/Resource/ACLOID')
                          FROM RESOURCE_VIEW r
                          WHERE equals_path(r.RES, '/TESTUSER/po1.xml') = 1));

    ANY_PATH
    ------------------
    /TESTUSER/acl1.xml

    1 row selected.

### Retrieving the Paths of All Resources Protected by a Given ACL

Example 24-12 retrieves the paths of all resources protected by a given ACL.

Example 24-12 Retrieving the Paths of All Resources Protected by a Given ACL

This example retrieves the paths to the resources whose `ACLOID` `REF` matches the `REF` of the ACL resource whose path is `/TESTUSER/acl1.xml`. Function `make_ref` returns the resource `ACLOID` `REF`.

The inner query retrieves the `REF` of the specified ACL. The outer query selects the paths of the resources whose `ACLOID` `REF` matches the `REF` of the specified ACL.

    SELECT r.ANY_PATH
      FROM RESOURCE_VIEW r
      WHERE make_ref(XDB.XDB$ACL, extractValue(r.RES, '/Resource/ACLOID'))
            = (SELECT extractValue(a.RES, '/Resource/XMLRef')
                 FROM RESOURCE_VIEW a
                 WHERE equals_path(a.RES, '/TESTUSER/acl1.xml') = 1);

    ANY_PATH
    -----------------
    /TESTUSER/po1.xml

    1 row selected.

## Integrating Oracle XML DB with LDAP

This section deals with allowing LDAP users to use the features of Oracle XML DB. The typical scenario is a single, shared database schema, to which multiple LDAP users are mapped. This mapping is maintained in the Oracle Internet Directory. Users can log in to the database using their LDAP username and password; they are then automatically mapped to the corresponding shared schema. (Users can log in using SQL or any of the supported Oracle XML DB protocols.) The implicit ACL resolution is based on the current LDAP user and the corresponding LDAP group membership information.

Before you can use LDAP users and groups as principals in Oracle XML DB ACLs, the following prerequisites must be satisfied:

- An Oracle Internet Directory must be set up, and the database must be registered with it.

- SSL authentication must be set up between the database and the Oracle Internet Directory.

- A database user must be created that corresponds to the shared database schema.

- The LDAP users must be created and mapped in the Oracle Internet Directory to the shared database schema.

- The LDAP groups must be created and their members must be specified.

- ACLs must be defined for the LDAP groups and users, and they must be used to protect the repository resources to be accessed by the LDAP users.

See Also: Oracle Internet Directory Administrator's Guide for information on setting up the Oracle Internet Directory and registering the database Oracle Database Advanced Security Administrator's Guide for information on setting up SSL authentication Oracle Database Enterprise User Administrator's Guide for information on using shared database schemas for enterprise (LDAP) users

Example 24-13 ACL Referencing an LDAP User

This is an example of an ACL for an LDAP user. Element `<principal>` contains the full distinguished name of the LDAP user – in this case, `cn=user1,ou=Americas,o=oracle,l=redwoodshores,st=CA,c=US`.

    <acl description="/public/txmlacl1/acl1.xml"
         xmlns="http://xmlns.oracle.com/xdb/acl.xsd" xmlns:dav="DAV:"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://xmlns.oracle.com/xdb/acl.xsd
                             http://xmlns.oracle.com/xdb/acl.xsd">
      <ace principalFormat="DistinguishedName">
        <principal>cn=user1,ou=Americas,o=oracle,l=redwoodshores,st=CA,c=US
        </principal>
        <grant>true</grant>
        <privilege>
          <dav:all/>
        </privilege>
      </ace>
    </acl>

See Also: Oracle Internet Directory Administrator's Guide for the format of an LDAP user distinguished name

Example 24-14 ACL Referencing an LDAP Group

This is an example of an ACL for an LDAP group. Element `<principal>` contains the full distinguished name of the LDAP group.

    <acl xmlns="http://xmlns.oracle.com/xdb/acl.xsd" xmlns:dav="DAV:"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://xmlns.oracle.com/xdb/acl.xsd
                             http://xmlns.oracle.com/xdb/acl.xsd">
      <ace principalFormat="DistinguishedName">
        <principal>cn=grp1,ou=Americas,o=oracle,l=redwoodshores,st=CA,c=US</principal>
        <grant>true</grant>
        <privilege>
          <dav:read/>
        </privilege>
      </ace>
    </acl>

See Also: Oracle Internet Directory Administrator's Guide for the format of an LDAP group distinguished name

## Performance Issues for Using ACLs

Since ACLs are checked for each access to Oracle XML DB Repository, the performance of the ACL check operation is critical to the performance of the repository. In Oracle XML DB, the required performance for this operation is achieved by employing several caches. ACLs are cached in a shared (shared by all sessions in the instance) cache. The performance of this cache is better when there are fewer ACLs in your system. Hence it is recommended that you share ACLs (between resources) as much as possible. Also, the cache works best when the number of ACEs in an ACL is at most 16.

There is also a session-specific cache of privileges granted to a given user by a given ACL. The entries in this cache have a time out (in seconds) specified by the element `<acl-max-age>` in the XDB configuration file (`/xdbconfig.xml`). For maximum performance this timeout should be as large as possible. But note that there is a trade-off here: the greater the timeout, the longer it will take for current sessions to pick up an updated ACL.

Oracle XML DB also maintains caches to improve performance when using ACLs that have LDAP principals (LDAP groups or users). The goal of these caches is to minimize network communication with the LDAP server. One is a shared cache that maps LDAP GUIDs to the corresponding LDAP nicknames and Distinguished Names (DNs). This is used when an ACL document is being displayed (or converted to `CLOB` or `VARCHAR`2 values from an `XMLType` instance). To purge this cache, use procedure `DBMS_XDBZ.PurgeLDAPCache`. The other cache is session-specific and maps LDAP groups to their members (nested membership). Note that whenever Oracle XML DB encounters an LDAP group for the first time (in a session) it will get the nested membership of that group from the LDAP server. Hence it is best to use groups with as few members and levels of nesting as possible.